#!/usr/bin/env python
# coding:utf-8


from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtWidgets import *
from PyQt5 import QtWidgets
import datetime,time
import uuid
import sys

import sqlite3
import xlrd


class Daoru(QWidget):

    def __init__(self):
        super(Daoru,self).__init__()

        self.con = sqlite3.connect('cov19.db')
        self.cur = self.con.cursor()
        self.resize(360,200)
        self.center()
        self.set_ui()


    def set_ui(self):

        self.filename = QLineEdit()
        self.filename.setStyleSheet('width:200px;height:30px;border:solid;')
        self.filename.setPlaceholderText("请选择excel文件")
        self.filename.setReadOnly(True)

        # label = QLabel("选择Excel作表(sheet):")
        self.table = QComboBox()
        table_list = ['身份证核查','个案表','关系表','行程表']
        for i in table_list:
            self.table.addItem(i)
        self.table.setStyleSheet("border:none;color:green;background:none;height:40px;")
        self.sheet_txt = QComboBox()
        self.sheet_txt.setStyleSheet("border:none;color:green;background:none;height:40px;")

        choose_bnt = QPushButton("选择文件")
        choose_bnt.clicked.connect(self.choose_file)
        choose_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#00BFFF')
        re_import_bnt = QPushButton('导入数据')
        re_import_bnt.clicked.connect(self.daoru)
        re_import_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#008000')
        reset_data_bnt = QPushButton("重置数据")
        reset_data_bnt.clicked.connect(self.reset_data)
        reset_data_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#313000')
        close_bnt = QPushButton('关闭窗口')
        close_bnt.clicked.connect(lambda:self.close())
        close_bnt.setStyleSheet('width:40px;height:30px;border:solid;color:white;background:#FF0000')

        layout = QGridLayout()
        layout.addWidget(self.filename,1,0,1,4)
        # layout.addWidget(QLabel(' '),1,0,1,4)
        layout.addWidget(self.table,0,0,1,2)
        layout.addWidget(self.sheet_txt,0,2,1,2)
        layout.addWidget(choose_bnt,3,0)
        layout.addWidget(re_import_bnt,3,1)
        layout.addWidget(reset_data_bnt,3,2)
        layout.addWidget(close_bnt,3,3)

        self.setLayout(layout)

    def choose_file(self):
        filename,ok = QFileDialog.getOpenFileName(self, '选择数据','./')
        if filename:
            self.filename.setText(filename)
            try:
                workbook = xlrd.open_workbook(filename)
                sheets = workbook.sheet_names()
                if sheets:
                    self.sheet_txt.clear()
                for i in sheets:
                    self.sheet_txt.addItem(i)
            except:
                self.sheet_txt.addItem("无sheet")


    def daoru(self):
        filename = self.filename.text()
        sheet_index = self.sheet_txt.currentIndex()
        mark = self.table.currentIndex()
        if filename:
            workbook = xlrd.open_workbook(filename)
            sheet = workbook.sheets()[sheet_index]
            rows = sheet.nrows
            cols = sheet.ncols

            for i in range(1,rows):

                tmp = [sheet.cell(i,k).value for k in range(cols)]


                if mark == 1:

                    try:
                        tmp[0] = str(int(tmp[0]))
                    except:
                        tmp[0] = tmp[0]
                    tmp[9] = self.change_date(tmp[9])
                    self.cur.execute('select * from person where id = "{}"'.format(tmp[3]))
                    dup_rslt = self.cur.fetchone()
                    if not dup_rslt:
                        sql = '''insert into person values (
                            "{}","{}","{}","{}","{}",{},"{}","{}","{}",
                            {},"{}","{}","{}","{}",0
                        )
                        '''.format(*tmp)
                    else:
                        status_list = ['初筛阳性','无症状感染','确诊','康复','疑似病例','临床诊断病例']
                        if  tmp[10] in status_list:
                            tmp2 = [tmp[0]] +tmp[2:13] + [tmp[1]]
                            sql = '''update person set serial = "{}",name = "{}",id="{}",
                                gender="{}",age={},tel="{}",address="{}",gps="{}",regist_date={},
                                present_status="{}",present_measure="{}",backup="{}" where uuid="{}"
                            '''.format(*tmp2)
                        else:
                            tmp2 = tmp[2:10] + [tmp[1]]
                            sql = '''update person set name = "{}",id="{}",
                                gender="{}",age={},tel="{}",address="{}",gps="{}",regist_date={}
                                where uuid="{}"
                            '''.format(*tmp2)
                    self.cur.execute(sql)
                if mark == 2:
                    if not tmp[0]:
                        tmp[0] = 0
                    tmp[3] = self.change_date(tmp[3])
                    tmp[1] = tmp[1].upper()
                    tmp[2] = tmp[2].upper()
                    self.cur.execute('select * from relation where uuid = "{}" and contact_uuid = "{}"'.format(tmp[1],tmp[2]))
                    dup_rslt = self.cur.fetchall()
                    if not dup_rslt:
                        sql = '''insert into relation values (
                            {},"{}","{}",{},"{}","{}","{}","{}"
                        )
                        '''.format(*tmp)
                        self.cur.execute(sql)

                if mark == 3:
                    tmp[2] = self.change_date(tmp[2])
                    tmp[3] = self.change_date(tmp[3])
                    tmp[0] = tmp[0].upper()
                    self.cur.execute('select * from location where uuid = "{}" and address = "{}"'.format(tmp[0],tmp[1]))
                    dup_rslt = self.cur.fetchall()
                    if not dup_rslt:
                        sql = '''insert into location values (
                        "{}","{}",{},{},"{}","{}","{}","{}"
                        )
                        '''.format(*tmp)
                        self.cur.execute(sql)
                if mark == 0:
                    id = tmp[3]
                    rslt = self.id_check(id)
                    if rslt:
                        print(tmp[1:3])
            try:
                self.con.commit()
                QMessageBox.information(self,'tips','success',QMessageBox.Yes,QMessageBox.Yes)
            except:
                pass


    def reset_data(self):

        self.cur.execute('delete from person')
        self.cur.execute('delete from relation')
        self.cur.execute('delete from travel')
        msg = QMessageBox.information(self,'tips','是否清空数据库',QMessageBox.Yes,QMessageBox.No)
        if msg == QMessageBox.Yes:
            self.con.commit()


    def change_date(self,time):
        try:
            tmp = (time-70*365-19)*86400 - 8*3600
        except:
            tmp = 1000000
        return tmp


    def id_check(self,id): #身份证号码验证
        id_upper = id.upper()
        if '\'' in id_upper:
            id_upper = id_upper[1:]
        try:
            date_standard = time.strptime(id_upper[6:14],'%Y%m%d')
        except:
            date_standard = 0
        if not date_standard:
            return '身份证错误'
        else:
            if len(id_upper) == 18:
                certify_number = ['1','0','X','9','8','7','6','5','4','3','2']
                std_number = [7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2]
                sum = 0
                for i in range(17):
                    sum += int(id_upper[i])*std_number[i]
                certify_rslt = certify_number[sum%11]
                if certify_rslt == id_upper[-1]:
                    return None
                else:
                    return '身份证错误'
            else:
                return '身份证错误'


    def center(self):
        self.screen = QDesktopWidget().screenGeometry()
        self.size = self.geometry()
        self.move(int((self.screen.width()-self.size.width())/2),
                 int((self.screen.height()-self.size.height())/2)
                  )


if __name__ == '__main__':

    app = QApplication(sys.argv)
    app.setWindowIcon(QIcon('./img/barcode.ico'))
    win = Daoru()
    win.show()
    sys.exit(app.exec_())
